package com.demo.constant;

public class SqlConstant {

    private static String dbUrl = "jdbc:mysql://10.24.67.188:3306/octopusscheduler_130?serverTimezone=CST";
    private static String username = "product";
    private static String password = "z6S0PSWjdxxNM9bS";
    // 写出结果的mysql表
    public static String duaByOsDDL = "CREATE TABLE dua_by_os (\n" +
            "  pub_id STRING,\n" +
            "  create_time STRING,\n" +
            "  os STRING,\n" +
            "  cnt BIGINT,\n" +
            "  PRIMARY KEY (pub_id,create_time,os) NOT ENFORCED\n" +
            ") WITH (\n" +
            "   'driver' = 'com.mysql.cj.jdbc.Driver',\n" +
            "   'connector' = 'jdbc',\n" +
            "   'table-name' = 'dua_by_os',\n" +
            "   'url' = '"+dbUrl+"',\n" +
            "   'username' = '"+username+"',\n" +
            "   'password' = '"+password+"'\n" +
            ")";
    public static String duaByChannelDDL = "CREATE TABLE dua_by_channel (\n" +
            "  pub_id STRING,\n" +
            "  channel_name STRING,\n" +
            "  create_time STRING,\n" +
            "  cnt BIGINT,\n" +
            "  PRIMARY KEY (pub_id,create_time,channel_name) NOT ENFORCED\n" +
            ") WITH (\n" +
            "   'driver' = 'com.mysql.cj.jdbc.Driver'," +
            "   'connector' = 'jdbc',\n" +
            "   'table-name' = 'dua_by_channel',\n" +
            "   'url' = '"+dbUrl+"',\n" +
            "   'username' = '"+username+"',\n" +
            "   'password' = '"+password+"'\n" +
            ")";
    public static String feeByOsDDL = "CREATE TABLE fee_by_os (\n" +
            "  pub_id STRING,\n" +
            "  create_time STRING,\n" +
            "  os STRING,\n" +
            "  cnt_uid BIGINT,\n" +
            "  amt DECIMAL(38, 22),\n" +
            "  cnt_order BIGINT,\n" +
            "  PRIMARY KEY (pub_id,create_time,os) NOT ENFORCED\n" +
            ") WITH (\n" +
            "   'driver' = 'com.mysql.cj.jdbc.Driver'," +
            "   'connector' = 'jdbc',\n" +
            "   'table-name' = 'fee_by_os',\n" +
            "   'url' = '"+dbUrl+"',\n" +
            "   'username' = '"+username+"',\n" +
            "   'password' = '"+password+"'\n" +
            ")";
    public static String feeByChannelDDL = "CREATE TABLE fee_by_channel (\n" +
            "  pub_id STRING,\n" +
            "  channel_name STRING,\n" +
            "  create_time STRING,\n" +
            "  cnt_uid BIGINT,\n" +
            "  amt DECIMAL(38, 22),\n" +
            "  cnt_order BIGINT,\n" +
            "  PRIMARY KEY (pub_id,channel_name,create_time) NOT ENFORCED\n" +
            ") WITH (\n" +
            "   'driver' = 'com.mysql.cj.jdbc.Driver'," +
            "   'connector' = 'jdbc',\n" +
            "   'table-name' = 'fee_by_channel',\n" +
            "   'url' = '"+dbUrl+"',\n" +
            "   'username' = '"+username+"',\n" +
            "   'password' = '"+password+"'\n" +
            ")";

    public static String duaByOs = " insert into dua_by_os SELECT\n" +
            "  pub_id,\n" +
            "  create_time,\n" +
            "  CASE os WHEN 0 THEN 'ios' ELSE 'android' END AS os,\n" +
            "  COUNT(DISTINCT user_id) AS cnt\n" +
            "FROM\n" +
            "  (SELECT\n" +
            "    app_id,\n" +
            "    sdk_user_id AS user_id,\n" +
            "    time_format(create_time) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'usdk_user_login_log'\n" +
            "  UNION ALL\n" +
            "  SELECT\n" +
            "    cast(app_id as string) as app_id,\n" +
            "    cast(user_id as string) as user_id,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'psdk_log_app_user' ) a \n" +
            "  LEFT JOIN pub_app_mapping FOR SYSTEM_TIME AS OF a.proctime as b\n" +
            "  ON a.app_id = b.app_id\n" +
            "  WHERE pub_id is not null" +
            "  GROUP BY os, create_time, pub_id";

    public static String duaByChannel = " insert into dua_by_channel SELECT\n" +
            "  pub_id,\n" +
            "  ch,\n" +
            "  create_time,\n" +
            "  COUNT(DISTINCT user_id) AS cnt\n" +
            "FROM\n" +
            "  (SELECT\n" +
            "    app_id,\n" +
            "    sdk_id AS channel_id,\n" +
            "    sdk_user_id AS user_id,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'usdk_user_login_log' and sdk_user_id <> '1231824'\n" +
            "  UNION ALL\n" +
            "  SELECT\n" +
            "    app_id,\n" +
            "    channel_id,\n" +
            "    user_id,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'psdk_log_app_user' ) a\n" +
            "  LEFT JOIN pub_app_mapping FOR SYSTEM_TIME AS OF a.proctime as b ON a.app_id = b.app_id\n" +
            "  LEFT JOIN channel_info FOR SYSTEM_TIME AS OF a.proctime as c ON a.channel_id = c.channel_id\n" +
            "  WHERE pub_id is not null and ch is not null \n" +
            "  GROUP BY ch, create_time, pub_id";

    public static String feeByOs = " insert into fee_by_os SELECT\n" +
            "  pub_id,\n" +
            "  create_time,\n" +
            "  CASE os WHEN 0 THEN 'ios' ELSE 'android' END AS os,\n" +
            "  COUNT(DISTINCT user_id) AS cnt_uid,\n" +
            "  SUM(cast(fee as decimal)/cast(er as decimal)/100) AS amt,\n" +
            "  COUNT(1) AS cnt_order\n" +
            "FROM\n" +
            "  (SELECT\n" +
            "    app_id,\n" +
            "    channel_id,\n" +
            "    user_id,\n" +
            "    fee,\n" +
            "    currency,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'psdk_trade_info'\n" +
            "  UNION ALL\n" +
            "  SELECT\n" +
            "    app_id,\n" +
            "    sdk_id AS channel_id,\n" +
            "    sdk_user_id AS user_id,\n" +
            "    fee,\n" +
            "    currency,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'usdk_trade') a\n" +
            "  LEFT JOIN pub_app_mapping FOR SYSTEM_TIME AS OF a.proctime as b ON a.app_id = b.app_id\n" +
            "  LEFT JOIN currency_rate FOR SYSTEM_TIME AS OF a.proctime as c ON a.currency = c.currency\n" +
            "  WHERE pub_id is not null\n" +
            "  GROUP BY pub_id,create_time,os";

    public static String feeByChannel = " insert into fee_by_channel SELECT\n" +
            "  pub_id,\n" +
            "  ch channel_name,\n" +
            "  create_time,\n" +
            "  COUNT(DISTINCT user_id) AS cnt_uid,\n" +
            "  SUM(cast(fee as decimal)/100) AS amt,\n" +
            "  COUNT(1) AS cnt_order\n" +
            "FROM\n" +
            "  (SELECT\n" +
            "    app_id,\n" +
            "    channel_id,\n" +
            "    user_id,\n" +
            "    fee,\n" +
            "    currency ,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'psdk_trade_info'\n" +
            "  UNION ALL\n" +
            "  SELECT\n" +
            "    app_id,\n" +
            "    sdk_id AS channel_id,\n" +
            "    sdk_user_id AS user_id,\n" +
            "    fee,\n" +
            "    currency ,\n" +
            "    time_format(audit_create) create_time,\n" +
            "    proctime\n" +
            "  FROM kafkaSourceTable where type = 'usdk_trade') a\n" +
            "  LEFT JOIN pub_app_mapping FOR SYSTEM_TIME AS OF a.proctime as b ON a.app_id = b.app_id\n" +
            "  LEFT JOIN currency_rate FOR SYSTEM_TIME AS OF a.proctime as c ON a.currency = c.currency\n" +
            "  LEFT JOIN channel_info FOR SYSTEM_TIME AS OF a.proctime as d ON a.channel_id = d.channel_id\n" +
            "  WHERE pub_id is not null and ch is not null \n" +
            "  GROUP BY ch, create_time, pub_id";

    //用于计算新增用户
    public static String loginUserSelect = "SELECT pub_id, \n"
        + "       user_id, \n"
        + "       channel_id, \n"
        + "       a.app_id,\n"
        + "       create_time \n"
        + "FROM\n"
        + " (SELECT\n"
        + "    app_id,\n"
        + "    sdk_id AS channel_id,\n"
        + "    sdk_user_id AS user_id,\n"
        + "    time_format(`@timestamp`) create_time,\n"
        + "    proctime\n"
        + "  FROM kafkaSourceTable where type = 'usdk_user_login_log' and sdk_user_id <> '1231824'\n"
        + "  UNION ALL\n"
        + "  SELECT\n"
        + "    app_id,\n"
        + "    channel_id,\n"
        + "    user_id,\n"
        + "    time_format(`@timestamp`) create_time,\n"
        + "    proctime\n"
        + "  FROM kafkaSourceTable where type = 'psdk_log_app_user' ) a\n"
        + "  LEFT JOIN pub_app_mapping FOR SYSTEM_TIME AS OF a.proctime as b ON a.app_id = b.app_id\n"
        + "  WHERE b.pub_id IS NOT NULL AND a.user_id IS NOT NULL AND a.channel_id IS NOT NULL AND a.app_id IS NOT NULL AND a.create_time IS NOT NULL"
        + "  ";

}
